package com.neusoft.chenhanhua.gqing;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

public class Sms_home_recommend_subject51 {
	@Test
	public void insert() {
		Connection conn=null;
		String passowd="root";
		String username="root";
		String driver="com.mysql.jdbc.Driver";
		String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
		try {
			Class.forName(driver);
			conn=DriverManager.getConnection(url,username,passowd);
		} catch (Exception e) {
			e.printStackTrace();
		}
		String sql="insert into Sms_home_recommend_subject(id,subject_id,subject_name,recommend_status,sort)values(2,2,3,4,5)";
		Statement st=null;
		try {
			st=conn.createStatement();
			int row=st.executeUpdate(sql);
			System.out.println(row>0?"添加成功":"添加失败");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (st!=null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	@Test 
	public void upate() {
		Connection conn=null;
		String username="root";
		String password="root";
		String driver="com.mysql.jdbc.Driver";
		String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&charaterEncoding=utf-8";
		
		try {
			//连接驱动类
			Class.forName(driver);
			conn=DriverManager.getConnection(url,username,password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		//准备sql命令
		String sql="update Sms_home_recommend_subject set subject_id=3 where id=1";
		Statement st=null;
		try {
			st=conn.createStatement();
			//执行语句
			int row=st.executeUpdate(sql);
			System.out.println(row>0?"修改成功":"修改失败");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (st!=null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	@Test
	public void  delete() {
		Connection conn=null;
		String username="root";
		String password="root";
		String driver="com.mysql.jdbc.Driver";
		String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
		try {
			Class.forName(driver);
			conn=DriverManager.getConnection(url,username,password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		//准备sql命令
		String sql="delete from Sms_home_recommend_subject where id=1";
		Statement st=null;
		try {
			st=conn.createStatement();
			int row=st.executeUpdate(sql);
			System.out.println(row>0?"删除成功":"删除失败");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (st!=null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	@Test
	   public void selet() {
		   Connection conn=null;
		   String username="root";
		   String password="root";
		   String driver="com.mysql.jdbc.Driver";
		   String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
		   try {
			Class.forName(driver);
			conn=DriverManager.getConnection(url,username,password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		   //准备sql命令
		   String sql="select * from Sms_home_recommend_subject";
		   Statement st=null;
		   ResultSet rs=null;
		   try {
			st=conn.createStatement();
			rs=st.executeQuery(sql);
			//处理结果集
			while (rs.next()) {
				//通过columnLabel列名或者别名获取,
				System.out.println(rs.getInt("id"));
				System.out.println(rs.getObject("subject_id"));
				System.out.println(rs.getObject("subject_name"));
				System.out.println(rs.getObject("recommend_status"));
				System.out.println(rs.getObject("sort"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (st!=null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		   
	   }

}
